package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.CourseSchedulingDetail;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.RepairCourse;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 排课详情 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-03-20
 */
public interface CourseSchedulingDetailMapper extends BaseMapper<CourseSchedulingDetail> {

    @Select("select count(id) as count from course_scheduling_detail where id in (select scheduling_detail_id from record_course where deleted=0 and grade_id = ${gradeId})")
    public Integer getSchedulingCountForRecord(@Param("gradeId") Long gradeId);

    @Select("DELETE from course_scheduling_detail where grade_id = ${gradeId} and school_date>= #{schoolDate} and status = 0 and arranging_way = 3 and id not in(select scheduling_detail_id from record_course where grade_id = ${gradeId} and deleted = 0 and scheduling_detail_id is not null)")
    public Long deleteForGradeId(@Param("gradeId") Long gradeId,@Param("schoolDate") LocalDate schoolDate);

    @Select("select count(id) as count from course_scheduling_detail where grade_id = ${gradeId} and school_date = #{schoolDate} and deleted = 0")
    public Long getIsAtForDate(@Param("gradeId")Long gradeId,@Param("schoolDate") LocalDate schoolDate);

    /**
     * 获取班级排课的最大课次+1（即下一次课次）
     * @param gradeId
     * @return
     */
    @Select("select (IFNULL(MAX(lesson_num),0)+1) as lesson_num from course_scheduling_detail where deleted=0 and grade_id = ${gradeId} and school_date <= #{schoolDate}")
    public abstract Integer getNextLessonNum(@Param("gradeId") Long gradeId,@Param("schoolDate") LocalDate schoolDate);




    /**
     * 获取一条数据，包含删除的
     * @param Id
     * @return
     */
    @Select("select * from course_scheduling_detail where id=${Id}")
    public abstract CourseSchedulingDetail getOneForId(@Param("Id") Long Id);

    @Select("select * from course_scheduling_detail where out_id=${OutId} limit 1")
    public abstract CourseSchedulingDetail getOneForOutId(@Param("OutId") Long OutId);

    @Update("update course_scheduling_detail set deleted= ${deleted} where id = ${id} ")
    public Integer updateDeleted(@Param("id") Long id,@Param("deleted") Integer deleted);

    /**
     * 获取排课与调课、补课、消课信息
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "`course_scheduling_detail`.`id` AS `id`,",
                      "`course_scheduling_detail`.`school_id` AS `school_id`,",
                      "`course_scheduling_detail`.`grade_id` AS `grade_id`,",
                      "`course_scheduling_detail`.`grade_name` AS `grade_name`,",
                      "`course_scheduling_detail`.`teacher_id` AS `teacher_id`,",
                      "`course_scheduling_detail`.`teacher_name` AS `teacher_name`,",
                      "`course_scheduling_detail`.`school_date` AS `school_date`,",
                      "`course_scheduling_detail`.`start_date_time` AS `start_date_time`,",
                      "`course_scheduling_detail`.`end_date_time` AS `end_date_time`,",
                      "`course_scheduling_detail`.`time_end` AS `time_end`,",
                      "`course_scheduling_detail`.`time_start` AS `time_start`,",
                      "`course_scheduling_detail`.`deleted` AS `deleted`,",
                      "`course_scheduling_detail`.`year_part` AS `year_part`,",
                      "`course_scheduling_detail`.`quarter` AS `quarter`,",
                      "`course_scheduling_detail`.`subjects_id` AS `subjects_id`,",
                      "`course_scheduling_detail`.`subjects_name` AS `subjects_name`,",
                      "`course_scheduling_detail`.`lesson_type` AS `lesson_type`,",
                      "`course_scheduling_detail`.`status` AS `status`,(",
                      "CASE",
                          "`course_scheduling_detail`.`type` ",
                          "WHEN 2 THEN",
                          "4 ELSE 1 ",
                        "END ",
                        ") AS `course_type`,",
                        "0 AS `student_id`,",
                        "1 AS `scheduling_type` ",
                      "FROM",
                        "`course_scheduling_detail` ",
                      "WHERE",
                        "( `course_scheduling_detail`.`deleted` = 0 and course_scheduling_detail.id = ${id}) UNION",
                      "SELECT",
                        "`repair_course`.`id` AS `id`,",
                        "`repair_course`.`school_id` AS `school_id`,",
                        "`repair_course`.`grade_id` AS `grade_id`,",
                        "`repair_course`.`grade_name` AS `grade_name`,",
                        "`repair_course`.`teacher_id` AS `teacher_id`,",
                        "`repair_course`.`teacher_name` AS `teacher_name`,",
                        "`repair_course`.`school_date` AS `school_date`,",
                        "`repair_course`.`start_date_time` AS `start_date_time`,",
                        "`repair_course`.`end_date_time` AS `end_date_time`,",
                        "`repair_course`.`time_end` AS `time_end`,",
                        "`repair_course`.`time_start` AS `time_start`,",
                        "`repair_course`.`deleted` AS `deleted`,",
                        "`repair_course`.`year_part` AS `year_part`,",
                        "`repair_course`.`quarter` AS `quarter`,",
                        "`repair_course`.`subjects_id` AS `subjects_id`,",
                        "`repair_course`.`subjects_name` AS `subjects_name`,",
                        "`repair_course`.`lesson_type` AS `lesson_type`,",
                        "`repair_course`.`status` AS `status`,",
                        "`repair_course`.`course_type` AS `course_type`,",
                        "`repair_course`.`student_id` AS `student_id`,",
                        "2 AS `scheduling_type` ",
                      "FROM",
                        "`repair_course` ",
                      "WHERE",
                      "(",
                      "`repair_course`.`deleted` = 0 and repair_course.id = ${id})",
            "</script>"})
    public List<RepairCourse> getSchedulingUnionRepairForId(@Param("id") Long id);


    /**
     * 获取学生的排课时间
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param nowDateTime
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t1.* ",
                    "FROM",
                    "course_scheduling_detail AS t1",
                    "INNER JOIN student_grade AS t2 ON t1.grade_id = t2.grade_id ",
                    "AND t2.STATUS = 1 ",
                    "AND t2.reading_status = 1 ",
                    "AND t2.year_part = ${yearPart} ",
                    "AND t2.quarter_num = ${quarterNum} ",
                    "AND t2.student_id = ${studentId} ",
                    "AND t2.deleted = 0",
                    "and t2.lesson_type =${lessonType}",
                    "LEFT JOIN repair_course AS t3 ON t1.id = t3.out_scheduling_detail_id ",
                    "AND t3.deleted = 0 ",
                    "AND t3.course_type = 4 ",
                    "WHERE",
                    "t1.start_date_time &gt;= '${nowDateTime}' ",
                    "AND t3.id IS NULL ",
                    "GROUP BY",
                    "t2.grade_id,",
                    "t1.id",
            "</script>"})
    public List<CourseSchedulingDetail> getSchedulingList(@Param("yearPart")Integer yearPart,
                                                          @Param("quarterNum")Integer quarterNum,
                                                          @Param("studentId")Long studentId,
                                                          @Param("lessonType")Integer lessonType,
                                                          @Param("nowDateTime")String nowDateTime
                                                          );

    @Select({"<script>",
            "select * from course_scheduling_detail ",
            "where",
            "`year_part` = ${yearPart} and `quarter` = ${quarterNum} and deleted = 0 and lesson_type = ${lessonType} and grade_id = ${gradeId}",
            "<when test='startDateTime!=null and startDateTime!=&apos;&apos; '>",
            "and start_date_time > #{startDateTime}",
            "</when>",
            "ORDER BY start_date_time asc",
            "</script>"
    })
    public abstract List<CourseSchedulingDetail> getSchedulingForGradeId(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("gradeId") Long gradeId,@Param("lessonType") Integer lessonType, @Param("startDateTime")String startDateTime);

}
